#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3
import csv
import os
import sys
from datetime import datetime

def export_sensor_data_to_csv(database_path='resource/db/robot_data.db', 
                             table_name='sensor_data',
                             csv_filename=None,
                             start_date=None,
                             end_date=None):
    """
    将传感器数据从SQLite数据库导出到CSV文件
    
    参数:
    database_path: 数据库文件路径
    table_name: 表名
    csv_filename: CSV文件名，如果为None则自动生成
    start_date: 开始日期 (格式: YYYY-MM-DD HH:MM:SS)
    end_date: 结束日期 (格式: YYYY-MM-DD HH:MM:SS)
    """
    
    # 如果没有指定CSV文件名，则根据当前时间生成
    if csv_filename is None:
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        csv_filename = f"sensor_data_{timestamp}.csv"
    
    # 检查数据库文件是否存在
    if not os.path.exists(database_path):
        print(f"错误: 数据库文件 {database_path} 不存在")
        return False
    
    try:
        # 连接到SQLite数据库
        conn = sqlite3.connect(database_path)
        cursor = conn.cursor()
        
        # 构建查询语句
        query = f"SELECT robot_id, timestamp, air_temperature, air_humidity, light_intensity, co2_concentration, soil_temperature, soil_moisture FROM {table_name}"
        params = []
        
        # 如果指定了日期范围，则添加WHERE条件
        if start_date and end_date:
            query += " WHERE timestamp BETWEEN ? AND ?"
            params.extend([start_date, end_date])
        elif start_date:
            query += " WHERE timestamp >= ?"
            params.append(start_date)
        elif end_date:
            query += " WHERE timestamp <= ?"
            params.append(end_date)
            
        # 按时间戳排序
        query += " ORDER BY timestamp"
        
        # 执行查询
        cursor.execute(query, params)
        
        # 获取列名
        column_names = [description[0] for description in cursor.description]
        
        # 写入CSV文件
        with open(csv_filename, 'w', newline='', encoding='utf-8') as csvfile:
            writer = csv.writer(csvfile)
            
            # 写入表头
            writer.writerow(column_names)
            
            # 写入数据行
            row_count = 0
            for row in cursor.fetchall():
                writer.writerow(row)
                row_count += 1
        
        # 关闭数据库连接
        conn.close()
        
        print(f"成功导出 {row_count} 行数据到 {csv_filename}")
        return True
        
    except sqlite3.Error as e:
        print(f"数据库错误: {e}")
        return False
    except Exception as e:
        print(f"导出过程中发生错误: {e}")
        return False

def print_usage():
    """打印使用说明"""
    print("用法:")
    print("  python3 export_sensor_data.py")
    print("  python3 export_sensor_data.py [数据库路径] [CSV文件名]")
    print("  python3 export_sensor_data.py [数据库路径] [CSV文件名] [开始时间] [结束时间]")
    print("\n示例:")
    print("  python3 export_sensor_data.py")
    print("  python3 export_sensor_data.py resource/db/robot_data.db")
    print("  python3 export_sensor_data.py resource/db/robot_data.db sensor_data.csv")
    print("  python3 export_sensor_data.py resource/db/robot_data.db sensor_data.csv '2023-01-01 00:00:00' '2023-12-31 23:59:59'")

if __name__ == "__main__":
    # 处理命令行参数
    database_path = 'resource/db/robot_data.db'  # 默认值
    csv_filename = None
    start_date = None
    end_date = None
    
    if len(sys.argv) > 1:
        if sys.argv[1] in ['-h', '--help', 'help']:
            print_usage()
            sys.exit(0)
        database_path = sys.argv[1]  # 第一个参数是数据库路径
    
    if len(sys.argv) > 2:
        csv_filename = sys.argv[2]  # 第二个参数是CSV文件名
    
    if len(sys.argv) > 4:
        start_date = sys.argv[3]  # 第三个参数是开始时间
        end_date = sys.argv[4]    # 第四个参数是结束时间
    
    # 执行导出操作
    success = export_sensor_data_to_csv(
        database_path=database_path,
        csv_filename=csv_filename,
        start_date=start_date,
        end_date=end_date
    )
    
    if not success:
        sys.exit(1)